﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace SqlServerDriver
{
	public class SqlServerOptionInterfaceImplementations : DbViewDriver.ISchemaInfo, DbViewDriver.ILookup
	{
		private SqlServerDataSource datasource;
		
		private List<DbView.FieldInfo> fields {get; set;}
		private string table="";

		public SqlServerOptionInterfaceImplementations(SqlServerDataSource driverIn)
		{
			this.datasource = driverIn;
		}
		//////////////////////////////////////////////////////
		// 
		public string ConnectionString
		{
			get
			{
				return datasource.ConnectionString;
			}
		}

		public DbView.FieldInfo[] GetSchema(string tableName)
		{
			if (fields == null || tableName != table)  // cache not valid condition
			{
				SqlTableSchema schema = Helper.MakeSchemaFor(tableName, datasource.ConnectionString);
				fields = new List<DbView.FieldInfo>();
				for (int idx = 0; idx < schema.Count; ++idx)
					fields.Add(schema[idx]);
			}
			return fields.ToArray();
		}
		////////////////////////////////////////////////////////////////////////////////
		// Hacky hack hack for execution speed on large databases.
		// given an array of tables return a map of table to arrays of the field info that matters.
		//
		public Dictionary<string, DbView.FieldInfo[]> GetBasicSchemas(string[] tableNames)
		{
			Dictionary<string, DbView.FieldInfo[]> map = new Dictionary<string,DbView.FieldInfo[]>();
			string search = string.Join("','", tableNames);
			string template = @"
select     case when sc.name='dbo' then t.name else sc.name+'.'+t.name end as [Table Name], 
           cols.name as [Field Name],
           type_name(cols.user_type_id) as [Type], 
           case when cols.max_length = -1 then 'MAX' 
                else convert(varchar(20), case when cols.system_type_id in (231,239) then cols.max_length/2 
                                               when cols.system_type_id in (59,60,62,106,108) then cols.precision
                                               else cols.max_length end) end as [Length],
           case when cols.system_type_id in (59,60,62,106,108) then cols.scale else null end [Dp],
          case when ind.is_primary_key = 1 then 'Yes' else '' end as [PKey?],
          case cols.is_nullable when 0 then 'No' else '' end as [Nulls?]
  from sys.columns cols
  join sys.tables t on cols.object_id = t.object_id
  left join sys.schemas sc on sc.schema_id = t.schema_id
  left join (select ic.object_id, ic.column_id, 
                    max(cast(is_primary_key as tinyint)) as is_primary_key, 
                    min(cast(is_primary_key as tinyint)) as is_non_pk_index
               from sys.index_columns ic 
               join sys.indexes ind on ind.object_id = ic.object_id and ind.index_id = ic.index_id 
               group by ic.object_id, ic.column_id) ind on ind.object_id = cols.object_id  and ind.column_id = cols.column_id 
where case when sc.name='dbo' then t.name else sc.name+'.'+t.name end in ('{0}')    
  order by  case when sc.name='dbo' then '    .'+t.name else sc.name+'.'+t.name end,
			cols.column_id
";
			string sql = string.Format(template, search);
			DataTable dt = Helper.GenericQuery(this.ConnectionString, sql);
			// collects data for a table
			string currentTable = ""; 
			List<DbView.FieldInfo> schema = new List<DbView.FieldInfo>();
			foreach (DataRow dr in dt.Rows)
			{
				String tableName = dr[0].ToString();
				String name = dr[1].ToString();
				String sqlType = dr[2].ToString();
				String leng = dr[3].ToString();
				String dp = dr[4].ToString();
				String pk = dr[5].ToString();
				String nulls = dr[6].ToString(); nulls = (nulls.Length == 0) ? "Yes" : nulls; // Special. For null column blank means Yes
				//
				int len = (leng.ToUpper() == "MAX") ? -1 :
						  (leng.Length == 0) ? 0 : // 2000 driver
						   Convert.ToInt32(leng);
				DbView.FieldInfo fi = new DbView.FieldInfo(name, sqlType, len);
				fi.IsPKey = this.TfFromData(pk);
				if (sqlType.ToUpper() == "NCHAR" || sqlType.ToUpper() == "NVARCHAR")
					fi.IsUnicode = true;
				fi.AllowsNulls = this.TfFromData(nulls);
				fi.Dp = (dp.Length == 0) ? 0 : Convert.ToInt32(dp);
				
				// turnover test
				if (tableName != currentTable)
				{
					// update if something to update. T except for first row
					if (!string.IsNullOrEmpty(currentTable))
					{
						map.Add(currentTable, schema.ToArray());
						schema.Clear();
					}
					currentTable = tableName;
				}
				schema.Add(fi);
			}
			// the last one is left to do.
			if (!string.IsNullOrEmpty(currentTable))
				map.Add(currentTable, schema.ToArray());
			return map;
		}

		String DataFromCell(DataRow dr, String columnName)
		{
			if (!dr.Table.Columns.Contains(columnName)) return "";
			return dr[columnName].ToString();
		}

		bool TfFromData(String data)
		{
			if (data.Length == 0) return false;  // empty means no
			char c = data[0];
			if (c == 'Y' || c == 'y' || c == 'T' || c == 't') return true;
			if (c == '1') return true;
			if (c == '0') return true;
			// Unless N or No then true
			if ((c == 'N' || c == 'n') && data.Length <= 2) return false;
			// handle other cases as they arise
			return true;
		}
		//////////////////////////////////////////////////////////////////

		public string[] GetTables()
		{
			// there is no filter
			string tableNameFilter = "";
			// get the short-form template for tables as this just needs names

			string template = SqlServerTemplates.AltTableTemplate(datasource.SqlVersion);
			string sql = SqlServerTemplates.Templates.ProcessFilterClause(template, tableNameFilter, this.datasource.IsCaseSensitive);
			DataTable dt = Helper.GenericQuery(ConnectionString, sql);
			List<string> tables = new List<string>();
			foreach (DataRow dr in dt.Rows)
			{
				tables.Add(dr[0].ToString());
			}
			return tables.ToArray();
		}

		public System.Data.DataTable GetFkInfo()
		{
			// get the standard FK template as it will do. (Cunning/oblique design!)
			string template = SqlServerTemplates.Templates.FetchTemplate(this.datasource.SqlVersion, DbView.Commands.Cmnd.ForeignKeys);
			// have to resolve the fancy lookup text
			string sql = SqlServerTemplates.Templates.ProcessFilterClause(template, "", this.datasource.IsCaseSensitive);
			DataTable dt = Helper.GenericQuery(ConnectionString, sql);
			return dt;
		}

		//////////////////////////////////////////////////////
		// 
		public DbViewDriver.ISchemaInfo GetSchemaInfo()
		{
			return this as DbViewDriver.ISchemaInfo;
		}

		public string BuildLookupCommand(string pkTable, string[] pkFields, string keyField, string lookupData)
		{
			string template = @"select [{0}] from {1} where {2} in ({3})";
			string fieldList = string.Join("],[",pkFields);
			string command = string.Format(template, fieldList, Helper.SafeTableName(pkTable), Helper.SafeSqlField(keyField), lookupData);
			// order by clause
			if (lookupData.Contains(",") )
			{
				string[] items = lookupData.Split(',');
				if (items.Length < 40)  // magic number. OrderBy threshold. More than this its not worth bothering with.
				{
					// but don't kill the sql engine
					string clause = "";
					for (int idx = 0; idx < items.Length; ++idx)
					{
						clause += string.Format("when {0} then {1} ", items[idx], idx);
					}
					string orderBy = string.Format(" order by case {0} {1} end", Helper.SafeSqlField(keyField), clause);
					command += orderBy;
				}
			}
			return command;
		}
	}
}
